namespace TSL.DAL
{
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Text;
    using TSL.Model;

    public class Buy_SSC
    {
        public void AddBuy_SSC(TSL.Model.Buy_SSC model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("TSL_Buy_SSC_ADD");
            db.AddInParameter(dbCommand, "expect", DbType.AnsiString, model.expect);
            db.AddInParameter(dbCommand, "protype", DbType.AnsiString, model.protype);
            db.AddInParameter(dbCommand, "allmoney", DbType.AnsiString, model.allmoney);
            db.AddInParameter(dbCommand, "onemoney", DbType.AnsiString, model.onemoney);
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, model.username);
            db.AddInParameter(dbCommand, "codes", DbType.AnsiString, model.codes);
            db.AddInParameter(dbCommand, "beishu", DbType.AnsiString, model.beishu);
            db.ExecuteNonQuery(dbCommand);
        }

        public void Delete(string ID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("TSL_Buy_SSC_Delete");
            db.AddInParameter(dbCommand, "ID", DbType.AnsiString, ID);
            db.ExecuteNonQuery(dbCommand);
        }

        public void DeleteAllDetail(int days)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("TSL_Buy_SSC_DeleteAllDetail");
            db.AddInParameter(dbCommand, "days", DbType.AnsiString, days);
            db.ExecuteNonQuery(dbCommand);
        }

        public void DeleteBuy_SSC(string ID)
        {
            List<string> strList = new List<string>();
            strList.Add("delete from Buy_SSC where ID = '" + ID + "'");
            DbHelperSQL.ExecuteSqlTran(strList);
        }

        public bool Exists(string name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Buy_SSC");
            strSql.Append(" where lottery_num=@name ");
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@name", SqlDbType.VarChar, 50) };
            parameters[0].Value = name;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        public bool Exists(string ssc_id, string lottery_num)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Buy_SSC");
            strSql.Append(" where lottery_num=@lottery_num and ssc_id=@ssc_id");
            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@ssc_id", SqlDbType.VarChar, 50), new SqlParameter("@lottery_num", SqlDbType.VarChar, 50) };
            parameters[0].Value = ssc_id;
            parameters[1].Value = lottery_num;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        public DataSet GetBuyHis(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 5 * from v_buy_ssc");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by id desc");
            return DatabaseFactory.CreateDatabase().ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        public DataSet GetBuyHisAll(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 5 * from v_Buy_SSC_All");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by id desc");
            return DatabaseFactory.CreateDatabase().ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        public List<TSL.Model.Buy_SSC> GetListArray(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_Buy_SSC ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by id desc");
            List<TSL.Model.Buy_SSC> list = new List<TSL.Model.Buy_SSC>();
            using (IDataReader dataReader = DatabaseFactory.CreateDatabase().ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (dataReader.Read())
                {
                    list.Add(this.ReaderBind(dataReader));
                }
            }
            return list;
        }

        public List<TSL.Model.Buy_SSC> GetListArray(string strWhere, string username,string regfrom, string stratetime, string endtime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_Buy_SSC where 1=1");
            if (strWhere.Trim() != "")
            {
                strSql.Append(strWhere);
            }

            strSql.Append("and  username in (select username from users where regfrom like  '%'+ @username + '%' and  username like '%'+ @regfrom + '%')");
            strSql.Append(" and convert(datetime,addtime)>=convert(datetime,@stratetime)");
            strSql.Append(" and convert(datetime,addtime)<=convert(datetime,@endtime)");
            strSql.Append(" order by id desc");

            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@username", SqlDbType.VarChar, 50), 
                new SqlParameter("@regfrom", SqlDbType.VarChar, 50), 
                new SqlParameter("@stratetime", SqlDbType.VarChar, 50),
                new SqlParameter("@endtime", SqlDbType.VarChar, 50)};
            parameters[0].Value = username;
            parameters[1].Value = regfrom;
            parameters[2].Value = stratetime;
            parameters[3].Value = endtime;

            List<TSL.Model.Buy_SSC> list = new List<TSL.Model.Buy_SSC>();
            using (IDataReader dataReader = TSL.DBUtility.DbHelperSQL.ExecuteReader(strSql.ToString(), parameters))
            {
                while (dataReader.Read())
                {
                    list.Add(this.ReaderBind(dataReader));
                }
            }
            return list;
        }

        public List<TSL.Model.Buy_SSC> GetListArray(string strWhere, string qihao, string stratetime, string endtime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_Buy_SSC where 1=1");
            if (strWhere.Trim() != "")
            {
                strSql.Append(strWhere);
            }

            strSql.Append(" and expect like '%'+ @qihao + '%' ");
            strSql.Append(" and convert(datetime,addtime)>=convert(datetime,@stratetime)");
            strSql.Append(" and convert(datetime,addtime)<=convert(datetime,@endtime)");
            strSql.Append(" order by id desc");

            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@qihao", SqlDbType.VarChar, 50), 
                new SqlParameter("@stratetime", SqlDbType.VarChar, 50),
                new SqlParameter("@endtime", SqlDbType.VarChar, 50)};
            parameters[0].Value = qihao;
            parameters[1].Value = stratetime;
            parameters[2].Value = endtime;

            List<TSL.Model.Buy_SSC> list = new List<TSL.Model.Buy_SSC>();
            using (IDataReader dataReader = TSL.DBUtility.DbHelperSQL.ExecuteReader(strSql.ToString(), parameters))
            {
                while (dataReader.Read())
                {
                    list.Add(this.ReaderBind(dataReader));
                }
            }
            return list;
        }

        public DataSet GetListFromView(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_Buy_SSC ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by id desc");
            return DatabaseFactory.CreateDatabase().ExecuteDataSet(CommandType.Text, strSql.ToString());
        }

        public TSL.Model.Buy_SSC GetModel(string username, int ID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_Buy_SSC_GetModelByID");
            db.AddInParameter(dbCommand, "ID", DbType.AnsiString, ID);
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, username);
            TSL.Model.Buy_SSC model = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    model = this.ReaderBindList(dataReader);
                }
            }
            return model;
        }

        public TSL.Model.Buy_SSC GetModel(string name, string password)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_Buy_SSC_GetModel");
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, name);
            db.AddInParameter(dbCommand, "password", DbType.AnsiString, password);
            TSL.Model.Buy_SSC model = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    model = this.ReaderBind(dataReader);
                }
            }
            return model;
        }

        public Buy_SSC_Info GetModelBy_Buy_SSC(string ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_HT_Buy_SSC ");
            if (ID.Trim() != "")
            {
                strSql.Append(" where id=" + ID);
            }
            strSql.Append(" order by id desc");
            Buy_SSC_Info model = null;
            using (IDataReader dataReader = DatabaseFactory.CreateDatabase().ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                if (dataReader.Read())
                {
                    model = this.ReaderBind_Buy_SSC(dataReader);
                }
            }
            return model;
        }

        public TSL.Model.Buy_SSC GetModelByID(string ID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_Buy_SSC_GetModelByLotteryID");
            db.AddInParameter(dbCommand, "ID", DbType.AnsiString, ID);
            TSL.Model.Buy_SSC model = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    model = this.ReaderBind(dataReader);
                }
            }
            return model;
        }

        public TSL.Model.Buy_SSC GetModels(int ID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_Buy_SSC_GetModelsByID");
            db.AddInParameter(dbCommand, "ID", DbType.AnsiString, ID);
            TSL.Model.Buy_SSC model = null;
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    model = this.ReaderBindList(dataReader);
                }
            }
            return model;
        }

        public List<Buy_SSC_Info> GetOpenSSC(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_HT_Buy_SSC ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by id desc");
            List<Buy_SSC_Info> list = new List<Buy_SSC_Info>();
            using (IDataReader dataReader = DatabaseFactory.CreateDatabase().ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (dataReader.Read())
                {
                    list.Add(this.ReaderBind_Buy_SSC(dataReader));
                }
            }
            return list;
        }

        public TSL.Model.Buy_SSC ReaderBind(IDataReader dataReader)
        {
            TSL.Model.Buy_SSC model = new TSL.Model.Buy_SSC();
            model.id = dataReader["id"].ToString();
            model.ssc_id = dataReader["ssc_id"].ToString();
            model.CHS_SSC = dataReader["ssc_name"].ToString();
            model.expect = dataReader["expect"].ToString();
            model.zhnum = dataReader["zhnum"].ToString();
            model.protype = dataReader["protype"].ToString();
            model.lotteryid = dataReader["lotteryid"].ToString();
            model.allmoney = dataReader["allmoney"].ToString();
            model.onemoney = dataReader["onemoney"].ToString();
            model.username = dataReader["username"].ToString();
            model.codes = dataReader["codes"].ToString();
            model.beishu = dataReader["beishu"].ToString();
            model.wininfo = Convert.ToInt32(dataReader["wininfo"].ToString());
            model.wincost = dataReader["wincost"].ToString();
            model.winid = dataReader["winid"].ToString();
            model.stzhuihao = dataReader["stzhuihao"].ToString();
            model.iszhuihao = dataReader["iszhuihao"].ToString();
            model.wczhuihao = dataReader["wczhuihao"].ToString();
            model.istop = dataReader["istop"].ToString();
            model.BeforZjIsStop = dataReader["BeforZjIsStop"].ToString();
            model.isreturn = dataReader["isreturn"].ToString();
            model.prostate = dataReader["prostate"].ToString();
            model.state = dataReader["state"].ToString();
            model.addtime = dataReader["addtime"].ToString();
            model.usertype = dataReader["usertype"].ToString();
            model.checkid = dataReader["checkid"].ToString();
            model.fdnum = dataReader["fdnum"].ToString();
            return model;
        }

        public Buy_SSC_Info ReaderBind_Buy_SSC(IDataReader dataReader)
        {
            Buy_SSC_Info model = new Buy_SSC_Info();
            model.id = dataReader["id"].ToString();
            model.ssc_id = dataReader["ssc_id"].ToString();
            model.expect = dataReader["expect"].ToString();
            model.zhnum = dataReader["zhnum"].ToString();
            model.protype = dataReader["protype"].ToString();
            model.lotteryid = dataReader["lotteryid"].ToString();
            model.allmoney = dataReader["allmoney"].ToString();
            model.onemoney = dataReader["onemoney"].ToString();
            model.username = dataReader["username"].ToString();
            model.codes = dataReader["codes"].ToString();
            model.beishu = dataReader["beishu"].ToString();
            model.wininfo = Convert.ToInt32(dataReader["wininfo"].ToString());
            model.wincost = dataReader["wincost"].ToString();
            model.winid = dataReader["winid"].ToString();
            model.stzhuihao = dataReader["stzhuihao"].ToString();
            model.iszhuihao = dataReader["iszhuihao"].ToString();
            model.wczhuihao = dataReader["wczhuihao"].ToString();
            model.istop = dataReader["istop"].ToString();
            model.BeforZjIsStop = dataReader["BeforZjIsStop"].ToString();
            model.isreturn = dataReader["isreturn"].ToString();
            model.prostate = dataReader["prostate"].ToString();
            model.state = dataReader["state"].ToString();
            model.addtime = dataReader["addtime"].ToString();
            model.usertype = dataReader["usertype"].ToString();
            model.checkid = dataReader["checkid"].ToString();
            model.fdnum = dataReader["fdnum"].ToString();
            model.CHS_state = dataReader["CHS_state"].ToString();
            model.CHS_SSC = dataReader["CHS_SSC"].ToString();
            model.CHS_CaiPiao = dataReader["CHS_CaiPiao"].ToString();
            model.num_info = dataReader["num_info"].ToString();
            return model;
        }

        public TSL.Model.Buy_SSC ReaderBindList(IDataReader dataReader)
        {
            TSL.Model.Buy_SSC model = new TSL.Model.Buy_SSC();
            model.id = dataReader["id"].ToString();
            model.ssc_id = dataReader["ssc_id"].ToString();
            model.expect = dataReader["expect"].ToString();
            model.zhnum = dataReader["zhnum"].ToString();
            model.protype = dataReader["protype"].ToString();
            model.lotteryid = dataReader["lotteryid"].ToString();
            model.allmoney = dataReader["allmoney"].ToString();
            model.onemoney = dataReader["onemoney"].ToString();
            model.username = dataReader["username"].ToString();
            model.codes = dataReader["codes"].ToString();
            model.beishu = dataReader["beishu"].ToString();
            model.wininfo = Convert.ToInt32(dataReader["wininfo"].ToString());
            model.wincost = dataReader["wincost"].ToString();
            model.winid = dataReader["winid"].ToString();
            model.stzhuihao = dataReader["stzhuihao"].ToString();
            model.iszhuihao = dataReader["iszhuihao"].ToString();
            model.wczhuihao = dataReader["wczhuihao"].ToString();
            model.istop = dataReader["istop"].ToString();
            model.BeforZjIsStop = dataReader["BeforZjIsStop"].ToString();
            model.isreturn = dataReader["isreturn"].ToString();
            model.prostate = dataReader["prostate"].ToString();
            model.state = dataReader["state"].ToString();
            model.addtime = dataReader["addtime"].ToString();
            model.usertype = dataReader["usertype"].ToString();
            model.checkid = dataReader["checkid"].ToString();
            model.fdnum = dataReader["fdnum"].ToString();
            model.CHS_state = dataReader["CHS_state"].ToString();
            model.CHS_SSC = dataReader["CHS_SSC"].ToString();
            model.CHS_CaiPiao = dataReader["CHS_CaiPiao"].ToString();
            model.num_info = dataReader["num_info"].ToString();
            return model;
        }

        public void Update(TSL.Model.Buy_SSC model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_Buy_SSC_Update");
            db.AddInParameter(dbCommand, "id", DbType.AnsiString, model.id);
            db.AddInParameter(dbCommand, "expect", DbType.AnsiString, model.expect);
            db.AddInParameter(dbCommand, "zhnum", DbType.AnsiString, model.zhnum);
            db.AddInParameter(dbCommand, "protype", DbType.AnsiString, model.protype);
            db.AddInParameter(dbCommand, "lotteryid", DbType.AnsiString, model.lotteryid);
            db.AddInParameter(dbCommand, "allmoney", DbType.AnsiString, model.allmoney);
            db.AddInParameter(dbCommand, "onemoney", DbType.AnsiString, model.onemoney);
            db.AddInParameter(dbCommand, "username", DbType.AnsiString, model.username);
            db.AddInParameter(dbCommand, "codes", DbType.AnsiString, model.codes);
            db.AddInParameter(dbCommand, "beishu", DbType.AnsiString, model.beishu);
            db.AddInParameter(dbCommand, "wininfo", DbType.AnsiString, model.wininfo);
            db.AddInParameter(dbCommand, "wincost", DbType.AnsiString, model.wincost);
            db.AddInParameter(dbCommand, "winid", DbType.AnsiString, model.winid);
            db.AddInParameter(dbCommand, "stzhuihao", DbType.AnsiString, model.stzhuihao);
            db.AddInParameter(dbCommand, "iszhuihao", DbType.AnsiString, model.iszhuihao);
            db.AddInParameter(dbCommand, "wczhuihao", DbType.AnsiString, model.wczhuihao);
            db.AddInParameter(dbCommand, "istop", DbType.AnsiString, model.istop);
            db.AddInParameter(dbCommand, "BeforZjIsStop", DbType.AnsiString, model.BeforZjIsStop);
            db.AddInParameter(dbCommand, "isreturn", DbType.AnsiString, model.isreturn);
            db.AddInParameter(dbCommand, "prostate", DbType.AnsiString, model.prostate);
            db.AddInParameter(dbCommand, "state", DbType.AnsiString, model.state);
            db.AddInParameter(dbCommand, "addtime", DbType.AnsiString, model.addtime);
            db.AddInParameter(dbCommand, "usertype", DbType.AnsiString, model.usertype);
            db.AddInParameter(dbCommand, "checkid", DbType.AnsiString, model.checkid);
            db.ExecuteNonQuery(dbCommand);
        }

        public void Update_Buy_SSC(Buy_SSC_Info model)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("SYS_Buy_SSC_Update_info");
            db.AddInParameter(dbCommand, "id", DbType.AnsiString, model.id);
            db.AddInParameter(dbCommand, "protype", DbType.AnsiString, model.protype);
            db.AddInParameter(dbCommand, "codes", DbType.AnsiString, model.codes);
            db.AddInParameter(dbCommand, "allmoney", DbType.AnsiString, model.allmoney);
            db.AddInParameter(dbCommand, "wincost", DbType.AnsiString, model.wincost);
            db.ExecuteNonQuery(dbCommand);
        }
    }
}

